CREATE VIEW [dbo].[vProcessTaskAssignee]
AS
SELECT TI.[TaskItemKey],CM.[FullName]
  FROM [dbo].[TaskItem] AS TI LEFT OUTER JOIN
       [dbo].[OpportunityMain] AS OM ON OM.[OpportunityKey] = TI.[OpportunityKey] LEFT OUTER JOIN
       [dbo].[GroupMemberDetail] AS OGMD ON OGMD.[GroupRoleKey] = TI.[AssignedToKey] AND OGMD.[GroupKey] = OM.[OpportunityOwnerGroupKey] INNER JOIN
       [dbo].[GroupMember] AS OGM ON OGM.[GroupMemberKey] = OGMD.[GroupMemberKey] INNER JOIN
       [dbo].[ContactMain] AS CM ON CM.[ContactKey] = OGM.[MemberContactKey]
UNION
SELECT TI.[TaskItemKey],CM.[FullName]
  FROM [dbo].[TaskItem] AS TI INNER JOIN
       [dbo].[ContactMain] AS CM ON CM.[ContactKey] = TI.[AssignedToKey]

GO